#!/bin/bash

# 数据库连接信息
DB_HOST="localhost"
DB_USER="root"  # 
DB_PASS="root"  # 

# 数据库名称
DB_NAME="backend_database_familiar_with_qujingmap"


# 创建数据库
echo "正在创建数据库 $DB_NAME..."
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" -e "CREATE DATABASE IF NOT EXISTS $DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

if [ $? -ne 0 ]; then
    echo "创建数据库失败！"
    exit 1
fi

echo "数据库 $DB_NAME 创建成功！"

# 创建user表
echo "正在创建Users表..."
<<EOF
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" -e CREATE TABLE Users(
  user_account CHAR(20) PRIMARY KEY,  
  user_name CHAR(20) NOT NULL,       
  user_password CHAR(20) NOT NULL,      
  register_time char(20) NOT NULL,  
  recently char(20) NOT NULL      
);
EOF
if [ $? -eq 0 ]; then
    echo "Users表创建成功！"
else
    echo "Users表创建失败！"
    exit 1
fi

echo "Users表创建完成！"



# 创建Examine_scores表
echo "正在创建Examine_scores表..."
<<EOF
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" -e CREATE TABLE Examine_scores (
    user_score_id INT AUTO_INCREMENT PRIMARY KEY,  
    user_account CHAR(20) NOT NULL,                     
    user_name CHAR(20) NOT NULL,                   
    question_count INT NOT NULL,                   
    user_grade INT NOT NULL,                       
    exam_date CHAR(20) NOT NULL,                    
    examine_true_num INT NOT NULL,                 
    examine_false_num INT NOT NULL,                
    CONSTRAINT fk_user_account  
        FOREIGN KEY (user_account) 
        REFERENCES Users(user_account) 
        ON DELETE CASCADE   
);
EOF
if [ $? -eq 0 ]; then
    echo "Examine_scores表创建成功！"
else
    echo "Examine_scores表创建失败！"
    exit 1
fi

echo "Examine_scores表创建完成！"





# 创建User_Question_Stats视图
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" -e echo "正在创建User_Question_Stats视图..."
<<EOF
CREATE VIEW User_Question_Stats AS
SELECT 
    user_account,
    user_name,
    -- 1. 总考核题目数量
    SUM(question_count) AS total_questions,
    SUM(examine_true_num) AS total_true_questions,
  SUM(examine_false_num) AS total_false_questions,
    -- 2. 本周考核题目数量（周一至周日）
    SUM(CASE 
        WHEN YEARWEEK(exam_date, 1) = YEARWEEK(CURDATE(), 1)  -- 1表示周一为一周开始
        THEN question_count 
        ELSE 0 
    END) AS weekly_questions,
  
  SUM(CASE 
        WHEN YEARWEEK(exam_date, 1) = YEARWEEK(CURDATE(), 1)  -- 本周正确数
        THEN examine_true_num 
        ELSE 0 
    END) AS weekly_true_questions,
    
    -- 3. 本月考核题目数量
    SUM(CASE 
        WHEN YEAR(exam_date) = YEAR(CURDATE()) AND MONTH(exam_date) = MONTH(CURDATE())
        THEN question_count 
        ELSE 0 
    END) AS monthly_questions,
  
  SUM(CASE 
        WHEN YEAR(exam_date) = YEAR(CURDATE()) AND MONTH(exam_date) = MONTH(CURDATE())   -- 本月正确数
        THEN examine_true_num 
        ELSE 0 
    END) AS monthly_true_questions,
  
  -- 4. 本日考核题目数量（新增）
    SUM(CASE 
        WHEN DATE(exam_date) = CURDATE()  -- 判断是否为当天日期
        THEN question_count 
        ELSE 0 
    END) AS daily_questions,
  
  SUM(CASE 
        WHEN DATE(exam_date) = CURDATE()  -- 本日正确数
        THEN examine_true_num 
        ELSE 0 
    END) AS daily_true_questions
  
FROM 
    Examine_scores
GROUP BY 
    user_account, user_name;
EOF
if [ $? -eq 0 ]; then
    echo "User_Question_Stats表创建成功！"
else
    echo "User_Question_Stats表创建失败！"
    exit 1
fi

echo "User_Question_Stats表创建完成！"

echo "数据库准备完毕！"